#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Zhangcl
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table,Column, Integer, String,ForeignKey, UniqueConstraint, Index,DATE,update,func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import and_, or_
from conf.setting import db_connect_info
engine = create_engine('mysql+pymysql://%s:%s@%s/%s?charset=utf8'  %(db_connect_info['user'],
                                                                     db_connect_info['passwd'],
                                                                     db_connect_info['host'],
                                                                     db_connect_info['db_name']))

#create database studydb charset "utf8"
Base = declarative_base()

class Students(Base):
    '''
    学生表
    '''
    __tablename__ = 'students'
    qq = Column(Integer,primary_key=True)
    name = Column(String(64))
    passwd = Column(String(64))

    def __repr__(self):
        return '%s %s'%(
        self.qq,self.name)

class Teacher(Base):
    '''
    教师表
    '''
    __tablename__ = 'teacher'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    passwd = Column(String(64))

    def __repr__(self):
        return '%s %s' %(
        self.id,self.name)

class Classes(Base):
    '''
    课程表
    '''
    __tablename__ = 'classes'
    id = Column(Integer,primary_key=True)
    name =Column(String(64))
    teacher_id =Column(Integer,ForeignKey('teacher.id'))

    teacher_info = relationship('Teacher',backref="class_info")

    def __repr__(self):
        return "%s  %s   %s  %s" % (
        self.id,self.name,self.teacher_id,self.teacher_info.name)


class Class_choise(Base):
    '''
    课程选择表
    '''
    __tablename__ ='class_choise'
    id = Column(Integer,primary_key=True)
    class_id = Column(Integer,ForeignKey('classes.id'))
    student_qq = Column(Integer,ForeignKey('students.qq'))

    classes=relationship('Classes',backref="class_choise_info")
    student=relationship('Students',backref="student_choise_info")

    def __repr__(self):
        return '%s  %s  %s %s %s' %(
        self.id,self.class_id,self.student_qq,self.classes.name,self.student.name)

class Class_record(Base):
    '''
    上课记录表
    '''
    __tablename__ = 'class_record'
    id = Column(Integer,primary_key=True)
    class_id = Column(Integer,ForeignKey('classes.id'))
    date = Column(DATE)
    teacher_id =Column(Integer,ForeignKey('teacher.id'))
    student_qq = Column(Integer,ForeignKey('students.qq'))

    classes = relationship('Classes',backref="class_record_info")
    teacher = relationship('Teacher',backref='teacher_record_info')
    student = relationship('Students',backref = 'student_record_info')

    def __repr__(self):
        return '%s %s %s %s %s %s %s %s '%(
            self.id,self.class_id,self.date,self.teacher_id,self.student_qq,self.classes.name,self.teacher.name,self.student.name
        )

class Homework(Base):
    '''
    作业表
    '''
    __tablename__ = 'homework'
    id = Column(Integer,primary_key=True)
    class_id = Column(Integer,ForeignKey('classes.id'))
    date = Column(DATE)
    student_qq = Column(Integer, ForeignKey('students.qq'))
    status = Column(String(64))

    classes = relationship('Classes', backref="class_homework_info")
    student = relationship('Students', backref='student_homework_record')

    def __repr__(self):
        return '%s %s %s %s %s %s %s  ' %(
            self.id,self.class_id,self.date,self.student_qq,self.status,self.classes.name,self.student.name
        )
class Grade(Base):
    '''
    成绩表
    '''
    __tablename__ = 'grade'
    id = Column(Integer,primary_key=True)
    class_id = Column(Integer,ForeignKey('classes.id'))
    date = Column(DATE)
    student_qq = Column(Integer, ForeignKey('students.qq'))
    grade = Column(Integer)
    status =Column(String(64))

    classes = relationship('Classes', backref="class_grade_info")
    student = relationship('Students', backref='student_grade_record')

    def __repr__(self):
        return '%s %s %s %s %s %s %s %s' %(
            self.id,self.class_id,self.date,self.student_qq,self.grade,self.status,self.classes.name,self.student.name
        )


class Admin(Base):
    '''
    管理员表
    '''
    __tablename__ = 'admin'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    passwd =Column(String(64))

    def __repr__(self):
        return '%s %s %s'%(self.id,self.name,self.passwd)

# Base.metadata.create_all(engine)#创建表
# session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
# session = session_class() #生成session实例

